create view [dbo].[view_全市工业用地清理成果] as
select 区县名称,
sum(集体面积) 集体面积,
sum(国有面积) 国有面积,
sum(开发边界内面积) 开发边界内面积,
sum(A) A,
sum(B) B,
sum(C) C,
sum(AB) AB,

sum(A1) A1,
sum(A2) A2,
sum(A3) A3,
sum(A4) A4,
sum(A5) A5,
sum(A11) A11,
sum(A12) A12,
sum(A13) A13,
sum(B1) B1,
sum(B2) B2,
sum(C1) C1,
sum(C2) C2
from
(
		select 区县名称 ,
		case when 权属性质='集体' then 面积 else 0 end as 集体面积,
		case when 权属性质='国有' then 面积 else 0 end as 国有面积,
		case when 城镇开发边界 is not null and 城镇开发边界<>'' then 面积 else 0 end as 开发边界内面积,
		case when 类型 LIKE '%A%' then 面积 else 0 end as A,
		case when 类型 LIKE '%B%' and 类型 not like '%B2%' then 面积 else 0 end as B,
		case when 类型 LIKE '%C%' then 面积 else 0 end as C,
		case when 类型 LIKE '%A%' OR  类型 LIKE '%B%' then 面积 else 0 end as AB,

		case when 类型 LIKE '%A1%' then 面积 else 0 end as A1,
		case when 类型 LIKE '%A2%' then 面积 else 0 end as A2,
		case when 类型 LIKE '%A3%' then 面积 else 0 end as A3,
		case when 类型 LIKE '%A4%' then 面积 else 0 end as A4,
		case when 类型 LIKE '%A5%' then 面积 else 0 end as A5,
		case when 类型 LIKE '%A11%' then 面积 else 0 end as A11,
		case when 类型 LIKE '%A12%' then 面积 else 0 end as A12,
		case when 类型 LIKE '%A13%' then 面积 else 0 end as A13,
		case when 类型 LIKE '%B1%' then 面积 else 0 end as B1,
		case when 类型 LIKE '%B2%' then 面积 else 0 end as B2,
		case when 类型 LIKE '%C1%' then 面积 else 0 end as C1,
		case when 类型 LIKE '%C2%' then 面积 else 0 end as C2

		from 
		(
			select 
			区县名称,面积/10000 面积,
			权属性质,
			城镇开发边界,
			case 
			when 类型 like '%A1%'  and  类型 not like '%A11%'  and 类型 not like '%A12%' and 类型 not like '%A13%' then 'A1'
			when 类型 like '%A2%' then 'A2'
			when 类型 like '%A3%' then 'A3'
			when 类型 like '%A4%' then 'A4'
			when 类型 like '%A5%' then 'A5'
			when 类型 like '%A11%' then 'A11'
			when 类型 like '%A12%' then 'A12'
			when 类型 like '%A13%' then 'A13'
			when 类型 like '%B1%' then 'B1'
			when 类型 like '%B2%' then 'B2'
			when 类型 like '%C1%' then 'C1'
			when 类型 like '%C2%' then 'C2' end as 类型
			from  [dbo].[全市工业用地清理成果20240725]

		) a
	
) a
group by 区县名称